# -*- coding: utf-8 -*-

import datetime
import logging
import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker

from cibynet.model import meta
from cibynet.model.all_objects import *


log = logging.getLogger(__name__)


def date_todatetime(d):
  return datetime.datetime(d.year, d.month, d.day)

def PopulateDBWithAdminAndSuperAdmin(engine):
  Session = sessionmaker(bind=engine, autocommit=True)
  session = Session()
  session.begin()
  u1 = User("Admin", "-", "-", "Admin", "Admin")
  u2 = User("SuperAdmin", "-", "", "SuperAdmin", "SuperAdmin")
  g1 = Group(u"admin", 100)
  g2 = Group(u"superadmin", 200)
  u1.groups.extend([g1])
  u2.groups.extend([g1, g2])
  session.add_all([g1, g2, u1, u2])
  session.commit()


def PopulateDBForProd(engine):
  """Populate the database from the old one.
  """
  from cibynet.utils import migration_cibynet
  Session = sessionmaker(bind=engine, autocommit=True)
  session = Session()
  session.begin()
  
  # Buildings
  buildings = {}
  for building in migration_cibynet.buildings:
    b = Building(building.label)
    buildings[building.bat_cle] = b
    session.add(b)
  
  # Rooms
  rooms = {}
  for room in migration_cibynet.rooms:
    r = Room(buildings[room.bat_cle], room.label)
    rooms[room.std_cle] = r
    session.add(r)
    
  # Students
  logins = {}
  students = {}
  for student in migration_cibynet.students:
    login = student.login or "anonymous"
    key = login.lower()
    if key in logins:
      logins[key] += 1
      newlogin = "%s%i" % (login, logins[key])
      while newlogin.lower() in logins:
        logins[key] += 1
        newlogin = "%s%i" % (login, logins[key])
      login = newlogin
      logins[newlogin.lower()] = 1
    else:
      logins[key] = 1
    u = User(student.firstname, student.lastname, student.email, login, 
             student.password, from_old_db=True)
    session.add(u)
    students[student.etu_cle] = u
    
  # Adhesions
  for adhesion in migration_cibynet.adhesions:
    student = students.get(adhesion.etu_cle)
    if student is None: continue
    room = rooms[adhesion.std_cle]
    begin = date_todatetime(adhesion.begin)
    end = None
    if not adhesion.active:
      end = date_todatetime(adhesion.end)
    assoc = RoomUser(room=room, from_date=begin, to_date=end)
    student.roomassociations.insert(0, assoc)
    student.active = True
    
  # Set students to active or not
  for student in students.itervalues():
    if student.room is None:
      student.active = False
    
  # IPs
  ips = {}
  for ip in migration_cibynet.ips:
    i = Ip(ip.address) #, available=not ip.booked)
    session.add(i)
    ips[ip.ipa_cle] = i
    
  # Machines
  machines = {}
  for machine in migration_cibynet.machines:
    student = students.get(machine.etu_cle)
    if not student: continue # If no student, don't put the machine in db.
    mac = migration_cibynet.getMac(machine)
    if mac: # if no mac for the machine, just consider there is no machine.
      m = Machine(student, machine.name, mac, active=machine.active)
      session.add(m)
      machines[machine.mac_cle] = m
      
  # IPs to Machines
  for mac in migration_cibynet.macs:
    ip = ips[mac.ipa_cle]
    begin = date_todatetime(mac.adr_datedeb)
    end = None
    if mac.active:
      try:
        a = MachineIp(ip=ip, from_date=begin)
      except AssertionError: # duplication in old interface...
        continue
    else:
      continue # It's a mess and not easy to d the two next lines
      end = date_todatetime(mac.adr_datefin)
      a = MachineIp(ip=ip, from_date=begin, to_date=end)
    machine = machines.get(mac.mac_cle)
    if machine: machine.ipassociations.append(a)
    
  # Switchs
  switchs = {}
  for switch in migration_cibynet.switchs:
    ip = ips.get(switch.ipa_cle)
    #if ip: ip = session.query(Ip).filter_by(address=ip.address).one()
    s = Switch(switch.name, ip, manageable=switch.administrable)
    session.add(s)
    switchs[switch.swt_cle] = s
    
  # Switch ports
  for switchport in migration_cibynet.switchports:
    switch = switchs[switchport.swt_cle]
    p = SwitchPort(switch, switchport.number)
    session.add(p)
    room = rooms.get(switchport.std_cle)
    if room: room.switchport = p
  
  session.commit()
  
  
def PopulateDBForTests(engine):
  """ Populate the database for tests.
  """
  
  Session = sessionmaker(bind=engine, autocommit=True)
  session = Session()
  session.begin()
  
  # Let's add some users
  pierre = User(u"Pierre", u'Ruyssen', "pierre.ruyssen_@gmail.com", u"Virtuo",
                u"password")
  mickael = User(u"Mickael", u"Dubois", "dubois_@test.com", u"Baloo", u"password")
  yang = User(u"Yang", u"Zhou", "lutece-fr_@gmail.com", u"Lutece", u"password")
  eloise = User(u"Éléoïse", u"Ùàçèxös", "eloise@gmail.com", u"Zozo", u"password")
  lenouveau = User(u"Toto", u"TOTO", "toto@titi.com", u"toto", u"password") # no room for him
  florient = User(u"Florient", u"Farcy", "flo@yahoo.com", u"Flo") # no more room
  users = [pierre, mickael, yang, eloise, lenouveau, florient]
  session.add_all(users)
  
  # Let's add come groups for key users:
  g1 = Group(u"admin", 100)
  g2 = Group(u"superadmin", 200)
  groups = [g1, g2]
  session.add_all(groups)
  
  # Let's add some IPs
  ips = [Ip("127.0.0.1")] + [Ip("172.17.17.%d" % i) for i in xrange(255)]
  session.add_all(ips)
  
  # Let's add some buildings
  bat231 = Building(u"Bat231")
  bat232 = Building(u"Bat232")
  bat233 = Building(u"Bat233")
  buildings = [bat231, bat232, bat233]
  session.add_all(buildings)
    
  # Let's add some rooms
  room2028 = Room(bat233, u"2028")
  room1064 = Room(bat233, u"1064")
  roomB110 = Room(bat232, u"B110")
  roomB143 = Room(bat232, u"B143")
  roomA234 = Room(bat231, u"A234")
  room3028 = Room(bat233, u"3028")
  roomA235 = Room(bat231, u"A235")
  rooms = [room2028, roomB110, roomB143, roomA234, room3028, roomA235]
  session.add_all(rooms)
    
  # Let's add some switchs
  s1 = Switch(u"sw233-2s1", ips[200])
  s2 = Switch(u"sw233-1n1", ips[201])
  s3 = Switch(u"sw231-1", ips[202])
  s4 = Switch(u"sw232-1", ips[203])
  switchs = [s1, s2, s3, s4]
  session.add_all(switchs)
  
  # Lets's add some switchports
  for s in switchs:
    session.add_all([SwitchPort(s, n) for n in xrange(48)])
      
  # Let's assign some switchports to some rooms
  room2028.switchport = s1.ports[28]
  room1064.switchport = s2.ports[14]
  roomB110.switchport = s3.ports[3]
  roomB143.switchport = s3.ports[34]
  roomA234.switchport = s4.ports[38]
  roomA235.switchport = s4.ports[41]
    
  # Let's assign some rooms to some users
  assoc = RoomUser(room=room1064, from_date=datetime.datetime(2006, 9, 1),
                        to_date=datetime.datetime(2008, 3, 31))
  florient.roomassociations.insert(0, assoc)
  assoc = RoomUser(room=room2028, from_date=datetime.datetime(2007, 9, 1), 
                      to_date=datetime.datetime(2008, 5, 31))
  pierre.roomassociations.insert(0, assoc)
  assoc = RoomUser(room=room1064, from_date=datetime.datetime(2008, 5, 31))
  pierre.roomassociations.insert(0, assoc)
  # Warning: when using association proxy, handle manually the active attribute
  pierre.active = True
  assoc = RoomUser(room=roomA235, from_date=datetime.datetime(2006, 9, 1),
                    to_date=datetime.datetime(2007,8,31))
  yang.roomassociations.insert(0, assoc)
  yang.room = roomB110
  mickael.room = roomB143
  eloise.room = room2028
  
  # Lets add some machines
  t42p = Machine(pierre, "virtuot42p", "00:11:22:33:44:55")
  x61s = Machine(pierre, "virtuox61s", "00:11:22:33:44:56")
  lut_l = Machine(yang, "lutecelaptop", "00:11:22:33:44:57")
  baloo = Machine(mickael, "baloo_laptop", "00:11:22:33:44:58")
  elo_c = Machine(eloise, "eloise_computer", "00:11:22:33:44:59")
  guest_p = Machine(pierre, "guest_pierre", "00:11:22:33:44:60")
  flo_l = Machine(florient, "flo_laptop", "00:11:22:33:44:61")
  toto_c = Machine(lenouveau, "toto_computer", "000000000000")
  machines = [t42p, x61s, lut_l, baloo, elo_c, guest_p, flo_l, toto_c]
  flo_l.active = False
  session.add_all(machines)
    
  # Let's attribute some IPs
#  t42p.ips.append(ips[0], from_date=datetime.datetime(2007, 9, 1))
#  x61s.ips.append(ips[1], from_date=datetime.datetime(2007, 9, 1),
#                  to_date=datetime.datetime(2008, 1, 1))
#  x61s.ips.append(ips[2], from_date=datetime.datetime(2008, 1, 1, 1))
  a1 = MachineIp(ip=ips[0], from_date=datetime.datetime(2007, 9, 1))
  t42p.ipassociations.insert(0, a1)
  a2 = MachineIp(ip=ips[1], from_date=datetime.datetime(2007, 9, 1),
                 to_date=datetime.datetime(2008, 1, 1))
  x61s.ipassociations.insert(0, a2)
  a3 = MachineIp(ip=ips[2], from_date=datetime.datetime(2008, 1, 1, 1))
  x61s.ipassociations.insert(0, a3)
  elo_c.ips.append(ips[1]) # not used anymore by x61s
  lut_l.ips.append(ips[3])
  baloo.ips.append(ips[4])
  
  # Let's attribute some groups
  pierre.groups.extend([g1, g2])
  mickael.groups.extend([g1, g2])
  yang.groups.extend([g1, g2])
  eloise.groups.append(g1)
  
  # Some feedback
  f = Feedback("/", "Wonderful home page!", pierre)
  session.add(f)
  
  # Let's add some banks
  BANKS = [
         u"Banque Populaire",
         u"BNP Paribas",
         u"BRED",
         u"Caisse d'épargne",
         u"Crédit agricole SA",
         u"Crédit mutuel",
         u"Crédit industriel et commercial (CIC)",
         u"Crédit du Nord",
         u"HSBC",
         u"La Poste",
         u"LCL",
         u"Société générale",
         ]
  banks = [Bank(name) for name in BANKS]
  session.add_all(banks)
  
  # Some checks:
  check_toto = Check(banks[0], "000029933", "Maman de Toto")
  
  # We need some pending registrations 
  # toto asked for a registration for the room 
  toto_r = Registration(check_toto, lenouveau, roomA234)
  session.add(toto_r)
  
  
  session.commit()
  
  
if __name__ == '__main__':
  datafile = "data/cibynet.sqlite"
  import commands
  # print "rm %s" % datafile, commands.getoutput("rm %s" % datafile)
  engine = sa.create_engine("sqlite:///"+datafile, echo=True)
  log.info("Deleting all the tables...")
  meta.metadata.drop_all(bind=engine)
  log.info(commands.getoutput("paster setup-app development.ini"))
  log.info("Populating the DB...")
  engine = sa.create_engine("sqlite:///"+datafile)
  #model.init_model(engine)
  PopulateDBForTests(engine)
  log.info("done.")
    